package com.hbut.bili.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.hbut.bili.entity.Video;

public class VideoDao {
	
	static {
		//1加载驱动
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 插入一条视频信息
	 * @param video
	 * @return
	 * @throws Exception
	 */
	public Integer inserVideo(Video video) throws Exception {
		//2获取连接
    	Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=FALSE&serverTimezone=UTC", "root", "123456");
    	//3获得语句执行者
    	Statement stItem = conn.createStatement();
		//4执行sql
    	int rsInt = stItem.executeUpdate("INSERT INTO video (aid, bvid, cid, pic, title, descript, gmt_create, gmt_update) VALUES ('"+ video.getAid() +"', '" + video.getBvid() + "', '" + video.getCid() + "', '" + video.getPic() + "', '" + video.getTitle() + "', '" + video.getDescript() + "', now(), now() )");
    	stItem.close();
    	conn.close();
		return rsInt;
	}
	
	/**
	 * 获取视频信息列表
	 * @return
	 * @throws Exception 
	 */
	public List<Video> selectVideoList() throws Exception {
		List<Video> videoList = new ArrayList<Video>();
		
		//2获取连接
    	Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=FALSE&serverTimezone=UTC", "root", "123456");
    	//3获得语句执行者
    	Statement stItem = conn.createStatement();
		
    	ResultSet rsVideo = stItem.executeQuery("SELECT * FROM video");
    	while(rsVideo.next()) {
    		Video  video = new Video();
    		video.setAid(rsVideo.getLong("aid"));
    		video.setBvid(rsVideo.getString("bvid"));
    		video.setCid(rsVideo.getString("cid"));
    		video.setPic(rsVideo.getString("pic"));
    		video.setTitle(rsVideo.getString("title"));
    		video.setDescript(rsVideo.getString("descript"));
    		
    		videoList.add(video);
    	}
    	rsVideo.close();
    	stItem.close();
    	conn.close();
		return videoList;
	}
	
	
	/**
	 * 根据主键获得一条视频信息
	 * @param aid
	 * @return
	 * @throws Exception
	 */
	public Video selectVideoById(Long aid) throws Exception {
		Video video = new Video();
		//2获取连接
    	Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=FALSE&serverTimezone=UTC", "root", "123456");
    	//3获得语句执行者
    	Statement stItem = conn.createStatement();
		
    	ResultSet rsVideo = stItem.executeQuery("SELECT * FROM video WHERE aid=" + aid);
    	while(rsVideo.next()) {
    		video.setAid(rsVideo.getLong("aid"));
    		video.setBvid(rsVideo.getString("bvid"));
    		video.setCid(rsVideo.getString("cid"));
    		video.setPic(rsVideo.getString("pic"));
    		video.setTitle(rsVideo.getString("title"));
    		video.setDescript(rsVideo.getString("descript"));
    	}
    	rsVideo.close();
    	stItem.close();
    	conn.close();
		return video;
	}
}
